package org.liulinlin.sejw.box.order;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
//import java.text.DateFormat;
//import java.text.SimpleDateFormat;
//import java.text.ParseException;

import org.liulinlin.sejw.box.*;
import org.liulinlin.sejw.db.*;

import org.osoa.sca.annotations.Destroy;
import org.osoa.sca.annotations.Init;

public class MakeShipperNoteServiceImpl implements MakeShipperNoteService {
	private DBConnectionManager connMgr;
	private BAF2SN baf2SN;
	private MSN2OB msn2OB;
	
	@Init
	public void init() {
		connMgr = DBConnectionManager.getInstance();
		baf2SN = BAF2SN.getInstance();
		msn2OB = MSN2OB.getInstance();
	}
	@Destroy
	public void destroy() {
		connMgr.release();
	}
	
	public String getOrderPlanID() {
		return baf2SN.getOrderPlanID();
	}
	public ShipperNote getShipperNoteByAdId(int adId) {
		ShipperNote res = null;
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("MakeShipperNoteService.getShipperNote()不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select count(*) from ShipperNote where AD_ID = '" + adId + "'");
		
			rs.next();
			if (rs.getInt(1) > 0) {		//若有该十联单，则返回，否则，返回null
				ResultSet rsSN = stmt.executeQuery("select * from ShipperNote where AD_ID = '" + adId + "'");
				rsSN.next();
				res = new ShipperNote(rsSN.getString("SN_ID"));
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	public ShipperNote getShipperNote() {
		ShipperNote res = null;
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("MakeShipperNoteService.getShipperNote()不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select count(*) from ShipperNote where AD_ID = '" + baf2SN.getADID() + "'");
		
			rs.next();
			if (rs.getInt(1) > 0) {		//若有该十联单，则返回，否则，返回null
				ResultSet rsSN = stmt.executeQuery("select * from ShipperNote where AD_ID = '" + baf2SN.getADID() + "'");
				rsSN.next();
				res = new ShipperNote(rsSN.getString("SN_ID"));
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	public AllocDetails getAllocDetails() {
		return new AllocDetails(baf2SN.getADID());
	}
	public OrderPlan getOrderPlan() {
		return new OrderPlan(baf2SN.getOrderPlanID());
	}
	public String putShipperNote(String id, String CYID, String initDateString, String dateOfExpiryString, String comments) {
		String res = "添加成功";
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("MakeShipperNoteService.putShipperNote()不能获得数据库连接");
			return null;
		}
		
		try {			
			Statement stmt = con.createStatement();
			/*
			ResultSet rsBAG = stmt.executeQuery("select count(*) from BoxAndGoods where SN_ID = '" + id + "'");
			rsBAG.next();
			if (rsBAG.getInt(1) <= 0) {
				putBoxAndGoods(CYID, id);
			}
			*/
			ResultSet rsSN = stmt.executeQuery("select count(*) from ShipperNote where SN_ID = '" + id + "'");
			rsSN.next();
			if (rsSN.getInt(1) <= 0) {			//若无记录，则新建
				stmt.executeUpdate("insert into SNStatus values(null, '" + id + "', '初始化', 'liulinlin', CURDATE(), '" + comments + "')");
				ResultSet rs = stmt.executeQuery("select LAST_INSERT_ID() from SNStatus");
				rs.next();
				stmt.executeUpdate("insert into ShipperNote values('" + id + "', '" + initDateString + "', '" + dateOfExpiryString + "', '" + rs.getInt(1) + "', '" + baf2SN.getADID() + "', 'null')");
				rs.close();
				
				//更新对应的分配详情状态
				stmt.executeUpdate("update AllocDetails set AD_Status = 3 where AD_ID = '" + baf2SN.getADID() + "'");
			} else {			//若有记录，则返回错误信息
				res = "ID为" + id + "的十联单已经存在！";
			}
			
			rsSN.close();
			stmt.close();
		} catch (SQLException e) {
			res = "SQL异常";
			e.printStackTrace();
		}
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	/**
	 * 得到场站CYID的n个未使用的箱型为type的集装箱
	 * @param CYID
	 * @return
	 */
	private List<Box> getUnUsedBoxes(String CYID, int n, String type) {
		List<Box> res = new ArrayList<Box>();
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("MakeShipperNoteService.getUnUsedBoxes不能获得数据库连接");
			return null;
		}
		
		try {	
			Statement stmt = con.createStatement();
			
			ResultSet rs = stmt.executeQuery("select * from Box where CY_ID = '" + CYID + "' and Box_Used = false and Box_Type = '" + type + "'");
			while (rs.next() && n-- > 0) {
				res.add(new Box(rs.getString("Box_ID")));
			}
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	public void putBoxAndGoods(String CYID, String snId) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("MakeShipperNoteService.putBoxAndGoods不能获得数据库连接");
			return;
		}
		
		AllocDetails allocDetails = new AllocDetails(baf2SN.getADID());
		Goods goods = allocDetails.getCommission().getGoods();
		
		List<Box> boxes = getUnUsedBoxes(CYID, allocDetails.getBigNum(), "40GP");
		try {
			Statement stmt = con.createStatement();
			
			for (int i = 0; i < boxes.size(); i++) {
				stmt.executeUpdate("insert into BoxAndGoods values(null, '" + boxes.get(i).getId() + "', '" + goods.getId() + "', " + goods.getGoodsNum().getGP40Details() + ", '" + snId + "')");
				stmt.executeUpdate("update Box set Box_Status = 1 where Box_ID = '" + boxes.get(i).getId() + "'");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		boxes = getUnUsedBoxes(CYID, allocDetails.getSmallNum(), "20GP");
		try {
			Statement stmt = con.createStatement();
			
			for (int i = 0; i < boxes.size(); i++) {
				stmt.executeUpdate("insert into BoxAndGoods values(null, '" + boxes.get(i).getId() + "', '" + goods.getId() + "', " + goods.getGoodsNum().getGP20Details() + ", '" + snId + "')");
				stmt.executeUpdate("update Box set Box_Status = 1 where Box_ID = '" + boxes.get(i).getId() + "'");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connMgr.freeConnection("shipping_export", con);
	}
	public List<BoxAndGoods> getBoxAndGoods(String snId) {
		List<BoxAndGoods> res = new ArrayList<BoxAndGoods>();
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("MakeShipperNoteService.getBoxAndGoods不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rsBAG = stmt.executeQuery("select * from BoxAndGoods where SN_ID = '" + snId + "'");
			while (rsBAG.next()) {
				res.add(new BoxAndGoods(rsBAG.getInt("BAG_ID")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		connMgr.freeConnection("shipping_export", con);
		return res;
	}
	public List<FeeItem> getFeeItems() {
		List<FeeItem> res = new ArrayList<FeeItem>();
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("MakeShipperNoteService.getFeeItem不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from FeeItem");
			while (rs.next()) {
				res.add(new FeeItem(rs.getInt("Seq_Id")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		connMgr.freeConnection("shipping_export", con);
		return res;
	}
	public CYInfo getCYInfo(String id) {
		return new CYInfo(id);
	}
	public void setSnId(String snId) {
		msn2OB.setSnId(snId);
	}
	public String getSnId() {
		return msn2OB.getSnId();
	}
	public void setSnComments(String snComments) {
		msn2OB.setSnComments(snComments);
	}
	public String getSnComments() {
		return msn2OB.getSnComments();
	}
	public void setCyId(String cyId) {
		msn2OB.setCyId(cyId);
	}
	public String getCyId() {
		return msn2OB.getCyId();
	}
	public void setSnDateOfExpiry(String snDateOfExpiry) {
		msn2OB.setSnDateOfExpiry(snDateOfExpiry);
	}
	public String getSnDateOfExpiry() {
		return msn2OB.getSnDateOfExpiry();
	}
	public String updateShipperNote(String snId, String blId) {
		String res = "更新成功！";
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("MakeShipperNoteService.updateShipperNote不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			//先插入提单，再更新提单号
			stmt.executeUpdate("insert into BLInfo values('" + blId + "', null, null, NOW(), null, 'liulinlin', NOW(), null)");
			stmt.executeUpdate("update ShipperNote set BL_ID = '" + blId + "' where SN_ID = '" + snId + "'");
			ResultSet rs = stmt.executeQuery("select * from ShipperNote where SN_ID = '" + snId + "'");
			rs.next();
			int adId = rs.getInt("AD_ID");
			stmt.executeUpdate("update AllocDetails set AD_Status = 4 where AD_ID = " + adId);
		} catch (SQLException e) {
			e.printStackTrace();
			res = "SQL异常！";
		}
		
		connMgr.freeConnection("shipping_export", con);
		return res;
	}
}
